package com.qingcheng.dao;

import com.qingcheng.pojo.goods.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface CategoryMapper extends Mapper<Category> {

    /*
    * 商品详情推荐显示
    * */
    @Select("SELECT sku.id,sku.name,sku.image,MIN(sku.price) price,spu.id spu_id FROM `tb_spu` spu \n" +
            "INNER JOIN  `tb_category` cate\n" +
            "INNER JOIN `tb_sku` sku \n" +
            "ON spu.category3_id=cate.id\n" +
            "WHERE spu.category3_id= #{category3} \n" +
            "AND spu.id=sku.spu_id\n" +
            "GROUP BY spu.id\n" +
            "ORDER BY RAND() LIMIT 6")
    List<Map> RandomRecommend(@Param("category3") Integer category3);

    /*
    * 今日推荐
    * */
    @Select("SELECT spu.id spuid,sku.* FROM tb_spu spu INNER JOIN tb_sku sku\n" +
            "WHERE spu.id=sku.spu_id\n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY sku.spu_id\n" +
            "ORDER BY RAND() LIMIT 5")
    List<Map> Today();

    /*
     *商品详情推荐的列表
     * */
    @Select("SELECT * FROM `tb_category` " +
            "WHERE parent_id=#{id}\n" +
            "LIMIT 8")
    List<TbCategory> lists(@Param("id")String id);

    /*查询家用电器下不同电器的售卖情况*/
    @Select("SELECT cate.* FROM tb_category cate INNER JOIN qingcheng_order.tb_order_item oi \n" +
            "INNER JOIN qingcheng_order.tb_order o \n" +
            "ON o.id=oi.order_id\n" +
            "WHERE o.pay_status='1' \n" +
            "AND cate.parent_id= #{parent_id}\n" +
            "AND o.create_time>= #{date1} \n" +
            "AND o.create_time<= #{date2}\n" +
            "AND oi.category_id2=cate.id\n" +
            "GROUP BY cate.name\n" +
            "LIMIT 6")
    public List<TbCategory> appliances(@RequestParam("parent_id")String parent_id,
            @RequestParam("date1") LocalDate date1,
            @RequestParam("date2") LocalDate date2);

    /*如果一个都没有卖出则使用这个*/
    @Select("    SELECT * FROM `tb_category`\n" +
            "    WHERE parent_id=#{parent_id}\n" +
            "    LIMIT 6\n")
    public List<TbCategory> Nullappliances(@RequestParam("parent_id")String parent_id);


    /*
    *循环查询个个热门分类的的商品
    * */
    @Select("SELECT DISTINCT spu.* FROM tb_category cate \n" +
            "INNER JOIN tb_spu spu\n" +
            "INNER JOIN qingcheng_order.tb_order o \n" +
            "INNER JOIN qingcheng_order.tb_order_item oi\n" +
            "ON oi.category_id3=cate.id\n" +
            "WHERE spu.category3_id=cate.id\n" +
            "AND o.create_time>= #{date1} \n" +
            "AND o.create_time<= #{date2} \n" +
            "AND o.pay_status='1'\n" +
            "AND cate.id= #{categoryid}\n" +
            "ORDER BY RAND() LIMIT 7")
    List<TbSpu> HotGoods(@RequestParam("categoryid") Integer categoryid,
                       @RequestParam("date1") LocalDate date1,
                       @RequestParam("date2") LocalDate date2);

    /*如果一个都没有卖出则使用这个*/
    @Select("SELECT DISTINCT spu.* FROM tb_category cate INNER JOIN tb_spu spu\n" +
            "WHERE spu.category3_id=cate.id\n" +
            "AND cate.id= #{categoryid}\n" +
            "ORDER BY RAND() LIMIT 7")
    public List<TbSpu> NullHotGoods(@RequestParam("categoryid") Integer categoryid);
}
